import pymysql

# 封装数据库工具类

class DBUtil(object):
    # 添加类属性
    conn = None
    @classmethod
    def __get_conn(cls):
    # 判断 conn 是否为空，如果是，再创建
        if cls.conn is None:
            print("创建了一个conn")
            cls.conn = pymysql.connect(host="mysql.sqlpub.com", port=3306,
                                        user="whzlearning", password="2c8bcb9077d49390", database="whzlearning",
                                        charset="utf8")

            # 返回 非空连接
            return cls.conn

    @classmethod
    def __close_conn(cls):
        # 判断，conn 不为空，需要关闭。
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None
            # 常用方法：查询一条
    @classmethod
    def select_one(cls, sql):
        cursor = None
        res = None
        try:
            # 获取连接
            cls.conn = cls.__get_conn()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行 查询语句
            cursor.execute(sql)
            # 提取一条结果
            res = cursor.fetchone()
        except Exception as err:
            print("查询sql错误：", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            # cls.__close_conn()
            # 将查询sql执行的 结果，返回
            return res

    @classmethod
    def selectFetchall(cls, sql):
         cursor = None
         try:
            # 获取连接
            cls.conn = cls.__get_conn()
            # 获取游标
            cursor = cls.conn.cursor()
            cls.cursor.execute(sql)
            result = cls.cursor.fetchall()
         except Exception as err:
             print("查询sql错误：", str(err))
         finally:
             # 关闭游标
             cursor.close()
             # 关闭连接
             # cls.__close_conn()
             # 将查询sql执行的 结果，返回
             return result

    @classmethod
    def selectFetchmany(cls, sql, num):
        cursor = None
        try:

            cls.conn = cls.__get_conn()
            # 获取游标
            cursor = cls.conn.cursor()

            cursor.execute(sql)

            result = cursor.fetchmany(num)

        except Exception as e:
            print("执行失败，err: ", e)

        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            # cls.__close_conn()

            return result



    @classmethod
    def crud(cls, sql):
        cursor = None
        try:

            cls.conn = cls.__get_conn()
            # 获取游标
            cursor = cls.conn.cursor()

            cursor.execute(sql)

            print("影响行数: ", cls.conn.affected_rows())

            cls.conn.commit()

        except Exception as e:

            print("执行失败，err: ", e)

            cls.conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            # cls.__close_conn()

        # 解决反复修改手机号